package cn.vconinfo.lims.delivery.mapper;

import cn.vconinfo.lims.delivery.entity.SpecimenCheckDTO;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

public interface SpecimenCheckMapper {

    @Select("<script>" +
            "SELECT COUNT(req.request_id) FROM basic_info info\n" +
            "LEFT JOIN specimen_request req\n" +
            "ON req.hospital_id = info.object_id\n" +
            "WHERE info.office_id = #{officeId}\n" +
            "<if test='placecode != null'> AND req.placeholder_code = #{placecode} </if> " +
            "AND info.`status` = 1\n" +
            "AND req.`status` = 1\n"+
            "</script>"
    )
    Long count4Table(@Param("officeId") Long officeId, @Param("placecode") String placecode);

    @Select("<script>" +
            "SELECT * FROM basic_info info\n" +
            "LEFT JOIN specimen_request req\n" +
            "ON req.hospital_id = info.object_id\n" +
            "WHERE info.office_id = #{officeId}\n" +
            "AND info.`status` = 1\n" +
            "AND req.`status` = 1\n" +
            "<if test='placecode != null'> AND req.placeholder_code = #{placecode} </if> " +
            "ORDER BY check_time DESC\n" +
            "LIMIT #{start}, #{pageSize}\n"+
            "</script>"
    )
    List<SpecimenCheckDTO> specimenCheckQuery(@Param("officeId") Long officeId, @Param("placecode") String placecode,
                                              @Param("start") Integer start, @Param("pageSize") Integer pageSize);


    @Select("SELECT object_id FROM basic_info\n" +
            "WHERE object_type = 2\n" +
            "AND office_id = #{officeId}")
    List<Long> getHospitalByOfficeId(@Param("officeId") Long officeId);

    @Select("SELECT count(*) FROM basic_type_info\n" +
            "WHERE type_info_type = 2\n" +
            "AND `status` = 1\n")
    Long countSpecimenInfo();

    @Select("SELECT COUNT(*) FROM specimen_application_link\n" +
            "WHERE request_id = #{requestId} and status =1 ")
    Long countDocInfo(@Param("requestId") Long requestId);


    @Select("SELECT * FROM specimen_request\n" +
            "WHERE `status` = 1\n")
    List<SpecimenCheckDTO> allSpecimenCheckQuery();

//    @Select("SELECT type_info_name, type_info_id FROM basic_type_info\n" +
//            "WHERE type_info_type = 3\n" +
//            "AND `status` = 1\n")
//    List<GeneralDTO> getDepartmentNameNId();

    @Insert("INSERT INTO specimen_application_link\n" +
            "(project_id, application_id, is_register, status, request_id)\n" +
            "VALUES\n" +
            "(#{projectId}, -1, 0, 1, #{requestId})\n")
    void specimenAppLinkInsertWithoutRegister(@Param("requestId") Long requestId, @Param("projectId") Long projectId);

    @Select("SELECT @@IDENTITY\n")
    Long getRequestIdForAppLinkInsert();

    @Select("SELECT application_link_id FROM specimen_application_link\n" +
            "WHERE request_id = #{requestId}\n" +
            "AND project_id = #{projectId} and status=1 ")
    Long getAppLinkId(@Param("requestId") Long requestId, @Param("projectId") Long projectId);

    @Select("SELECT type_info_id FROM basic_type_info\n" +
            "WHERE type_info_name = #{item_name}\n" +
            "AND type_info_type = 3\n" +
            "AND `status` = 1")
    Long getAppDeptIdForSpecimenCheckInsert(@Param("item_name") String item_name);


    @Select("SELECT type_info_name FROM basic_type_info\n" +
            "WHERE type_info_id = #{item_id}\n" +
            "AND `status` = 1")
    String getTypeInfoName(@Param("item_id") Long item_id);



    @Select("SELECT type_info_id FROM basic_type_info\n" +
            "WHERE type_info_name LIKE #{stringSex}\n" +
            "AND type_info_type = 4")
    Long getSexId(@Param("stringSex") String stringSex);

    @Select("SELECT type_info_id FROM basic_type_info\n" +
            "WHERE type_info_name LIKE #{ageunit}\n" +
            "AND type_info_type = 1")
    Long getAgeunit(@Param("ageunit") String ageunit);

    @Select("SELECT type_info_name FROM basic_type_info\n" +
            "WHERE type_info_id =#{typeInfoId}\n" +
            "AND type_info_type = 1")
    String getAgeName(@Param("typeInfoId") Long typeInfoId);

    @Select("SELECT type_info_id FROM basic_type_info\n" +
            "WHERE type_info_name LIKE #{stringAgeUnit}\n" +
            "AND type_info_type = 1")
    Long getAgeUnitId(@Param("stringAgeUnit") String stringAgeUnit);
    @Select("SELECT type_info_id FROM basic_type_info\n" +
            "WHERE type_info_name = #{item_name}\n" +
            "AND type_info_type = 2\n" +
            "AND `status` = 1")
    Long getSpecimenTypeForSpecimenCheckInsert(@Param("item_name") String item_name);

    @Select("SELECT doctor_id FROM hospital_doctor\n" +
            "WHERE doctor_name = #{bill_name}\n" +
            "AND hospital_id = #{hospitalId}\n" +
            "AND `status` = 1")
    Long getDocIdForSpecimenCheckInsert(@Param("bill_name") String bill_name, @Param("hospitalId") Long hospitalId);

    @Insert("INSERT INTO specimen_request\n" +
            "(placeholder_code, outpatient_id, patient_name, patient_sex, patient_age, patient_age_type,\n" +
            "patient_id, bed_num, hospital_id, patient_type, specimen_type, tube_type, application_department_id, application_doctor_id,\n" +
            "application_time, specimen_window, specimen_man_id, sampling_time, execute_department_id, check_man_id,\n" +
            "patient_tag, diagnosis, patient_id_card_num, remarks,pcode , daily_code,institution,addr,patient_tel )\n" +
            "VALUES\n" +
            "(#{placeholderCode}, #{outpatientId}, #{patientName}, #{patientSex}, #{patientAge}, #{patientAgeType},\n" +
            " #{patientId}, #{bedNum}, #{hospitalId}, #{patientType}, #{specimenType}, #{tubeType}, #{applicationDepartmentId}, #{applicationDoctorId},\n" +
            " #{applicationTime}, #{specimenWindow}, #{specimenManId}, #{samplingTime}, #{executeDepartmentId}, #{checkManId},\n" +
            " #{patientTag}, #{diagnosis}, #{patientIdCardNum}, #{remarks}, #{pcode},#{dailyCode},#{institution},#{addr},#{ptel} )")
    void specimenCheckInsert(@Param("placeholderCode") String placeholderCode,
                             @Param("outpatientId") String outpatientId, @Param("patientName") String patientName,
                             @Param("patientSex") Long patientSex, @Param("patientAge") Long patientAge,
                             @Param("patientAgeType") Long patientAgeType, @Param("patientId") String patientId,
                             @Param("bedNum") String bedNum, @Param("hospitalId") Long hospitalId,
                             @Param("patientType") Integer patientType, @Param("specimenType") Integer specimenType,
                             @Param("tubeType") String tubeType, @Param("applicationDepartmentId") Long applicationDepartmentId,
                             @Param("applicationDoctorId") Long applicationDoctorId, @Param("applicationTime") Date applicationTime,
                             @Param("specimenWindow") Integer specimenWindow, @Param("specimenManId") Integer specimenManId,
                             @Param("samplingTime") Date samplingTime, @Param("executeDepartmentId") Integer executeDepartmentId,
                             @Param("checkManId") Integer checkManId, @Param("patientTag") Integer patientTag,
                             @Param("diagnosis") String diagnosis, @Param("patientIdCardNum") String patientIdCardNum,
                             @Param("remarks") String remarks, @Param("pcode") String pcode, @Param("dailyCode") String dailyCode,
                             @Param("institution") String institution, @Param("addr") String addr, @Param("ptel") String ptel);

    @Insert("INSERT INTO specimen_request\n" +
            "(placeholder_code, outpatient_id, patient_name, patient_sex, patient_age, patient_age_type,\n" +
            "patient_id, bed_num, hospital_id, patient_type, specimen_type, tube_type, application_department_id, application_doctor_id,\n" +
            "application_time, specimen_window, specimen_man_id, sampling_time, execute_department_id, check_man_id,\n" +
            "patient_tag, diagnosis, patient_id_card_num, remarks, mannal, patient_tel,daily_code)\n" +
            "VALUES\n" +
            "(#{placeholderCode}, #{outpatientId}, #{patientName}, #{patientSex}, #{patientAge}, #{patientAgeType},\n" +
            " #{patientId}, #{bedNum}, #{hospitalId}, #{patientType}, #{specimenType}, #{tubeType}, #{applicationDepartmentId}, #{applicationDoctorId},\n" +
            " #{applicationTime}, #{specimenWindow}, #{specimenManId}, #{samplingTime}, #{executeDepartmentId}, #{checkManId},\n" +
            " #{patientTag}, #{diagnosis}, #{patientIdCardNum}, #{remarks}, #{mannal}, #{patientTel},#{dailyCode})")
    long addRequest(@Param("placeholderCode") String placeholderCode,
                    @Param("outpatientId") String outpatientId, @Param("patientName") String patientName,
                    @Param("patientSex") Long patientSex, @Param("patientAge") Long patientAge,
                    @Param("patientAgeType") Long patientAgeType, @Param("patientId") String patientId,
                    @Param("bedNum") String bedNum, @Param("hospitalId") Long hospitalId,
                    @Param("patientType") Integer patientType, @Param("specimenType") Integer specimenType,
                    @Param("tubeType") String tubeType, @Param("applicationDepartmentId") Long applicationDepartmentId,
                    @Param("applicationDoctorId") Long applicationDoctorId, @Param("applicationTime") Date applicationTime,
                    @Param("specimenWindow") Integer specimenWindow, @Param("specimenManId") Integer specimenManId,
                    @Param("samplingTime") Date samplingTime, @Param("executeDepartmentId") Integer executeDepartmentId,
                    @Param("checkManId") Integer checkManId, @Param("patientTag") Integer patientTag,
                    @Param("diagnosis") String diagnosis, @Param("patientIdCardNum") String patientIdCardNum,
                    @Param("remarks") String remarks, @Param("mannal") long mannal, @Param("patientTel") String patientTel,
                    @Param("dailyCode") String dailyCode, @Param("institution") String institution, @Param("addr") String addr
    );

    @Insert("INSERT INTO specimen_request\n" +
            "(placeholder_code, outpatient_id, patient_name, patient_sex, patient_age, patient_age_type,\n" +
            "patient_id, bed_num, hospital_id, patient_type, specimen_type, tube_type, application_department_id, application_doctor_id,\n" +
            "application_time, specimen_window, specimen_man_id, sampling_time, execute_department_id, check_man_id,\n" +
            "patient_tag, diagnosis, patient_id_card_num, remarks, mannal, patient_tel,daily_code,birth_date)\n" +
            "VALUES\n" +
            " ( "+
            " #{placeholderCode}," +
            " #{outpatientId}, #{patientName}, #{patientSex},\n " +
            " #{patientAge}, #{patientAgeType}, #{patientId}, \n " +
            " #{bedNum}, #{hospitalId}, #{patientType},\n " +
            " #{specimenType}, #{tubeType}, #{applicationDepartmentId},\n" +
            " #{applicationDoctorId}, #{applicationTime}, #{specimenWindow},\n" +
            " #{specimenManId}, #{samplingTime}, #{executeDepartmentId},\n" +
            " #{checkManId},  #{patientTag}, #{diagnosis},\n" +
            " #{patientIdCardNum}, #{remarks},1,#{patientTel},\n" +
            " #{dailyCode}, #{birthDate} "+
            " ) "
            )
    @Options(useGeneratedKeys = true, keyProperty = "requestId" ,keyColumn = "request_id")
    long addRequestAndReturnId(SpecimenCheckDTO specimenCheckDTO);


    @Update("UPDATE specimen_request\n" +
            "SET  placeholder_code = #{placeholderCode}, outpatient_id = #{outpatientId}, patient_name = #{patientName}, " +
            "patient_sex = #{patientSex}, patient_age =#{patientAge}, patient_age_type =#{patientAgeType} ,\n" +
            "patient_id = #{patientId}, bed_num = #{bedNum}, hospital_id = #{hospitalId}, patient_type = #{patientType}, " +
            "specimen_type = #{specimenType}, tube_type = #{tubeType}, application_department_id =#{applicationDepartmentId}, " +
            "application_doctor_id = #{applicationDoctorId},\n" +
            "application_time = #{applicationTime}, specimen_window = #{specimenWindow}, specimen_man_id = #{specimenManId}, " +
            "sampling_time =  #{samplingTime}, execute_department_id = #{executeDepartmentId}, check_man_id = #{checkManId},\n" +
            "patient_tag = #{patientTag}, diagnosis = #{diagnosis}, patient_id_card_num =#{patientIdCardNum}," +
            "patient_tel=#{patientTel},birth_date=#{birthDate}, " +
            "remarks = #{remarks} WHERE request_id = #{requestid}")
    long editRequest(@Param("placeholderCode") String placeholderCode,
                     @Param("outpatientId") String outpatientId, @Param("patientName") String patientName,
                     @Param("patientSex") Long patientSex, @Param("patientAge") Long patientAge,
                     @Param("patientAgeType") Long patientAgeType, @Param("patientId") String patientId,
                     @Param("bedNum") String bedNum, @Param("hospitalId") Long hospitalId,
                     @Param("patientType") Integer patientType, @Param("specimenType") Integer specimenType,
                     @Param("tubeType") String tubeType, @Param("applicationDepartmentId") Long applicationDepartmentId,
                     @Param("applicationDoctorId") Long applicationDoctorId, @Param("applicationTime") Date applicationTime,
                     @Param("specimenWindow") Integer specimenWindow, @Param("specimenManId") Integer specimenManId,
                     @Param("samplingTime") Date samplingTime, @Param("executeDepartmentId") Integer executeDepartmentId,
                     @Param("checkManId") Integer checkManId, @Param("patientTag") Integer patientTag,
                     @Param("diagnosis") String diagnosis, @Param("patientIdCardNum") String patientIdCardNum,
                     @Param("remarks") String remarks, @Param("requestid") long requestid
            , @Param("patientTel") String patientTel, String birthDate
    );




//    @Select("SELECT type_info_id, type_info_name FROM basic_type_info\n" +
//            "WHERE type_info_type = 2\n" +
//            "AND `status` = 1")
//    List<GeneralDTO> getSpecimenInfo();

    @Select("SELECT project_id FROM specimen_application_link\n" +
            "WHERE request_id = #{requestId} and status = 1\n")
    List<Long> getDocCode(@Param("requestId") Long requestId);

    @Select("SELECT project_document_id FROM hospital_project_link\n" +
            "WHERE hospital_document_id = #{hospitalDocumentId}\n" +
            "AND object_id = #{hospitalId}")
    List<Long> getDocId(@Param("hospitalId") Long hospitalId, @Param("hospitalDocumentId") String hospitalDocumentId);


//    @Select("SELECT * FROM hospital_project_link \n" +
//            "WHERE object_id = #{hospitalId} \n")
//    List<HospitalProjectLinkDTO> getHPQuery(@Param("hospitalId") Long hospitalId);

//    @Select("SELECT project_document_name, project_document_code, project_document_id FROM sys_project_document\n" +
//            "WHERE project_document_id = #{docId}\n" +
//            "AND `status` = 1")
//    ProjectDocumentDTO getDocInfo(@Param("docId") Long docId);

//    @Select("SELECT hospital_group_id, hospital_code AS hospital_id_in_group FROM hospital_link\n" +
//            "where hospital_id = #{hospitalId}")
//    HospitalGroupDTO getHosGroupIdNCode(@Param("hospitalId") Long hospitalId);

    @Select("SELECT hospital_group_url FROM hospital_group\n" +
            "WHERE hospital_group_id = #{hospitalGroupId}")
    String getHosGroupUrl(@Param("hospitalGroupId") Long hospitalGroupId);

    @Select("SELECT COUNT(*) FROM specimen_request\n" +
            "WHERE placeholder_code = #{placeholderCode}\n" +
            "AND hospital_id = #{hospitalId}")
    Long isExist(@Param("placeholderCode") String placeholderCode, @Param("hospitalId") Long hospitalId);

//    @Select("SELECT * FROM basic_info\n " +
//            "WHERE object_type = 2\n" +
//            "AND `status` = 1\n" +
//            "AND office_id = #{officeId}")
//    List<ObjectDTO> getHospitalInfo(@Param("officeId") Long officeId);

    @Select("SELECT count(*) FROM specimen_request sr \n" +
            "JOIN basic_info bi ON bi.object_id = sr.hospital_id \n" +
            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id \n" +
            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id \n" +
            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id \n" +
            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0 \n" +
            "AND sal.project_id=#{pid}\n" +
            "AND sr.hospital_id= #{hid} " +
            "AND sr.placeholder_code = #{placeholderCode}\n" +
            "AND to_days(application_time) = to_days(now()); ")
    Long hasSample(@Param("placeholderCode") String placeholderCode,
                   @Param("hid") long hid,
                   @Param("pid") long pid);

    @Select("<script>" +
            "SELECT count(*) FROM specimen_request sr \n" +
            "JOIN basic_info bi ON bi.object_id = sr.hospital_id \n" +
            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id \n" +
            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id \n" +
            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id \n" +
            "WHERE sr.status = 1 AND sal.status = 1 " +
//            "AND sal.is_register = 0 \n" +
            "AND sal.project_id=#{pid}\n" +
            "AND sr.hospital_id= #{hid} " +
            "<if test='placeholderCode != null'> AND sr.placeholder_code = #{placeholderCode} </if> " +
            "<if test='patient_name != null'> AND sr.patient_name = #{patient_name} </if> " +
            "AND to_days(application_time) = to_days(now()); " +
            "</script>" )
    Long hasSampleForRequest(@Param("placeholderCode") String placeholderCode,
                             @Param("hid") long hid,
                             @Param("pid") long pid, @Param("patient_name") String patientname);




}
